package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.MarketCarInfoTempDto;

public class MarketCarInfoTempDaoImp extends ViewListDao implements MarketCarInfoTempDao  {
	protected MarketCarInfoTempDaoImp(int pageNo, int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}
	
	
	public MarketCarInfoTempDaoImp() throws TmsDaoException{
		super();
	}
	
	@Override
	public List<MarketCarInfoTempDto> loadPadHistory() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<MarketCarInfoTempDto> padList = new ArrayList<MarketCarInfoTempDto>();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID,");
		sql.append("VIN,");
		sql.append("PHOTO_VIN,");
		sql.append("PHOTO_1,");
		sql.append("INSERTUSER,");
		sql.append("TEMPLICENSEPLATE,");
		sql.append("INSERTTIME");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMSAPP_MARKET_CAR_INFO_TEMP);
		sql.append(" WHERE ");
		sql.append(" ISMATCH=1 ");
		sql.append(" ORDER BY INSERTTIME");
		if (isAsc()){
			sql.append(" ASC ");
		}else{
			sql.append(" DESC ");
		}
		try {
			ps = conn.prepareStatement(getSql(sql.toString()));
			rs = ps.executeQuery();
			while(rs.next()){
				MarketCarInfoTempDto dto = new MarketCarInfoTempDto();
				dto.setId(rs.getLong("ID"));
				dto.setVin(rs.getString("VIN"));
				dto.setVinPhoto(rs.getString("PHOTO_VIN"));
				dto.setCarPhoto(rs.getString("PHOTO_1"));
				dto.setInsertUser(rs.getString("INSERTUSER"));
				dto.setTempLicensePlate(rs.getString("TEMPLICENSEPLATE"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				padList.add(dto);
			}
			return padList;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	public MarketCarInfoTempDto findCarTempInfoById(long id)throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		MarketCarInfoTempDto dto = new MarketCarInfoTempDto();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("VIN,");
		sql.append("PHOTO_VIN,");
		sql.append("PHOTO_1,");
		sql.append("PHOTO_2,");
		sql.append("PHOTO_3,");
		sql.append("PHOTO_4,");
		sql.append("PHOTO_5,");
		sql.append("PHOTO_6,");
		sql.append("PHOTO_7,");
		sql.append("PHOTO_8,");
		sql.append("ATTESTSTATUS,");
		sql.append("CHECKSTATUS,");
		sql.append("CHECKIMGPATH,");
		sql.append("INSERTUSER,");
		sql.append("INSERTTIME,");
		sql.append("SERIAL,");
		sql.append("TEMPLICENSEPLATE,");
		sql.append("CHECKPHOTO_1,");
		sql.append("CHECKPHOTO_2,");
		sql.append("CHECKPHOTO_3,");
		sql.append("CHECKPHOTO_4,");
		sql.append("CHECKPHOTO_5,");
		sql.append("CHECKPHOTO_6,");
		sql.append("CHECKPHOTO_7,");
		sql.append("CHECKPHOTO_8,");
		sql.append("CHECKPHOTO_9,");
		sql.append("CHECKPHOTO_10,");
		sql.append("CHECKPHOTO_11,");
		sql.append("CHECKPHOTO_12,");
		sql.append("CHECKPHOTO_13,");
		sql.append("CHECKPHOTO_14,");
		sql.append("CHECKPHOTO_15,");
		sql.append("MILEAGE,");
		sql.append("ISMATCH,");
		sql.append("TAG1SKIP,");
		sql.append("TAG2SKIP,");
		sql.append("TAG3SKIP,");
		sql.append("COLOR");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMSAPP_MARKET_CAR_INFO_TEMP);
		sql.append(" WHERE ");
		sql.append("ID=? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				dto.setVin(rs.getString("VIN"));
				dto.setVinPhoto(rs.getString("PHOTO_VIN"));
				dto.setPhoto1(rs.getString("PHOTO_1"));
				dto.setPhoto2(rs.getString("PHOTO_2"));
				dto.setPhoto3(rs.getString("PHOTO_3"));
				dto.setPhoto4(rs.getString("PHOTO_4"));
				dto.setPhoto5(rs.getString("PHOTO_5"));
				dto.setPhoto6(rs.getString("PHOTO_6"));
				dto.setPhoto7(rs.getString("PHOTO_7"));
				dto.setPhoto8(rs.getString("PHOTO_8"));
				dto.setAttestStatus(rs.getInt("ATTESTSTATUS"));
				dto.setCheckStatus(rs.getInt("CHECKSTATUS"));
				dto.setCheckImgPath(rs.getString("CHECKIMGPATH"));
				dto.setInsertUser(rs.getString("INSERTUSER"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setTempLicensePlate(rs.getString("TEMPLICENSEPLATE"));
				dto.setCheckPhoto_1(rs.getString("CHECKPHOTO_1"));
				dto.setCheckPhoto_2(rs.getString("CHECKPHOTO_2"));
				dto.setCheckPhoto_3(rs.getString("CHECKPHOTO_3"));
				dto.setCheckPhoto_4(rs.getString("CHECKPHOTO_4"));
				dto.setCheckPhoto_5(rs.getString("CHECKPHOTO_5"));
				dto.setCheckPhoto_6(rs.getString("CHECKPHOTO_6"));
				dto.setCheckPhoto_7(rs.getString("CHECKPHOTO_7"));
				dto.setCheckPhoto_8(rs.getString("CHECKPHOTO_8"));
				dto.setCheckPhoto_9(rs.getString("CHECKPHOTO_9"));
				dto.setCheckPhoto_10(rs.getString("CHECKPHOTO_10"));
				dto.setCheckPhoto_11(rs.getString("CHECKPHOTO_11"));
				dto.setCheckPhoto_12(rs.getString("CHECKPHOTO_12"));
				dto.setCheckPhoto_13(rs.getString("CHECKPHOTO_13"));
				dto.setCheckPhoto_14(rs.getString("CHECKPHOTO_14"));
				dto.setCheckPhoto_15(rs.getString("CHECKPHOTO_15"));
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setIsMatch(rs.getInt("ISMATCH"));
				dto.setColor(rs.getInt("COLOR"));
				dto.setTag1Skip(rs.getInt("TAG1SKIP"));
				dto.setTag2Skip(rs.getInt("TAG2SKIP"));
				dto.setTag3Skip(rs.getInt("TAG3SKIP"));
			}
			return dto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	public void updateCarMatchInfo(MarketCarInfoTempDto dto)throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMSAPP_MARKET_CAR_INFO_TEMP);
		sql.append(" SET ");
		sql.append(" ISMATCH=?, ");
		sql.append(" SERIAL=? ");
		sql.append(" WHERE ");
		sql.append(" ID=? ");
		try {			
			ps = conn.prepareStatement(sql.toString());
			
			ps.setInt(1, dto.getIsMatch());
			ps.setString(2, dto.getSerial());
			ps.setLong(3, dto.getId());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		}finally {
			super.close(rs, ps);
		}
	}
	public void updateCarDeleteInfo(long id,int isMatch)throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMSAPP_MARKET_CAR_INFO_TEMP);
		sql.append(" SET ");
		sql.append(" ISMATCH=?");		
		sql.append(" WHERE ");
		sql.append(" ID=? ");
		try {			
			ps = conn.prepareStatement(sql.toString());
			
			ps.setInt(1, isMatch);
			ps.setLong(2, id);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		}finally {
			super.close(rs, ps);
		}
	}
	public MarketCarInfoTempDto findCarTempInfoByVin(String vin)throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		MarketCarInfoTempDto dto = new MarketCarInfoTempDto();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID,");
		sql.append("VIN,");
		sql.append("PHOTO_VIN,");
		sql.append("PHOTO_1,");
		sql.append("PHOTO_2,");
		sql.append("PHOTO_3,");
		sql.append("PHOTO_4,");
		sql.append("PHOTO_5,");
		sql.append("PHOTO_6,");
		sql.append("PHOTO_7,");
		sql.append("PHOTO_8,");
		sql.append("ATTESTSTATUS,");
		sql.append("CHECKSTATUS,");
		sql.append("CHECKIMGPATH,");
		sql.append("INSERTUSER,");
		sql.append("INSERTTIME,");
		sql.append("SERIAL,");
		sql.append("CHECKPHOTO_1,");
		sql.append("CHECKPHOTO_2,");
		sql.append("CHECKPHOTO_3,");
		sql.append("CHECKPHOTO_4,");
		sql.append("CHECKPHOTO_5,");
		sql.append("CHECKPHOTO_6,");
		sql.append("CHECKPHOTO_7,");
		sql.append("CHECKPHOTO_8,");
		sql.append("CHECKPHOTO_9,");
		sql.append("CHECKPHOTO_10,");
		sql.append("CHECKPHOTO_11,");
		sql.append("CHECKPHOTO_12,");
		sql.append("CHECKPHOTO_13,");
		sql.append("CHECKPHOTO_14,");
		sql.append("CHECKPHOTO_15,");
		sql.append("MILEAGE,");
		sql.append("ISMATCH,");
		sql.append("TAG1SKIP,");
		sql.append("TAG2SKIP,");
		sql.append("TAG3SKIP,");
		sql.append("TEMPLICENSEPLATE,");
		sql.append("COLOR");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMSAPP_MARKET_CAR_INFO_TEMP);
		sql.append(" WHERE ");	
		sql.append(" VIN LIKE '%" + vin + "' ");
		sql.append(" AND ISMATCH = 1 ");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				dto.setId(rs.getLong("ID"));
				dto.setVin(rs.getString("VIN"));
				dto.setVinPhoto(rs.getString("PHOTO_VIN"));
				dto.setPhoto1(rs.getString("PHOTO_1"));
				dto.setPhoto2(rs.getString("PHOTO_2"));
				dto.setPhoto3(rs.getString("PHOTO_3"));
				dto.setPhoto4(rs.getString("PHOTO_4"));
				dto.setPhoto5(rs.getString("PHOTO_5"));
				dto.setTempLicensePlate(rs.getString("TEMPLICENSEPLATE"));
				dto.setPhoto6(rs.getString("PHOTO_6"));
				dto.setPhoto7(rs.getString("PHOTO_7"));
				dto.setPhoto8(rs.getString("PHOTO_8"));
				dto.setAttestStatus(rs.getInt("ATTESTSTATUS"));
				dto.setCheckStatus(rs.getInt("CHECKSTATUS"));
				dto.setCheckImgPath(rs.getString("CHECKIMGPATH"));
				dto.setInsertUser(rs.getString("INSERTUSER"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setCheckPhoto_1(rs.getString("CHECKPHOTO_1"));
				dto.setCheckPhoto_2(rs.getString("CHECKPHOTO_2"));
				dto.setCheckPhoto_3(rs.getString("CHECKPHOTO_3"));
				dto.setCheckPhoto_4(rs.getString("CHECKPHOTO_4"));
				dto.setCheckPhoto_5(rs.getString("CHECKPHOTO_5"));
				dto.setCheckPhoto_6(rs.getString("CHECKPHOTO_6"));
				dto.setCheckPhoto_7(rs.getString("CHECKPHOTO_7"));
				dto.setCheckPhoto_8(rs.getString("CHECKPHOTO_8"));
				dto.setCheckPhoto_9(rs.getString("CHECKPHOTO_9"));
				dto.setCheckPhoto_10(rs.getString("CHECKPHOTO_10"));
				dto.setCheckPhoto_11(rs.getString("CHECKPHOTO_11"));
				dto.setCheckPhoto_12(rs.getString("CHECKPHOTO_12"));
				dto.setCheckPhoto_13(rs.getString("CHECKPHOTO_13"));
				dto.setCheckPhoto_14(rs.getString("CHECKPHOTO_14"));
				dto.setCheckPhoto_15(rs.getString("CHECKPHOTO_15"));
				dto.setMileage(rs.getInt("MILEAGE"));
				dto.setIsMatch(rs.getInt("ISMATCH"));
				dto.setColor(rs.getInt("COLOR"));
				dto.setTag1Skip(rs.getInt("TAG1SKIP"));
				dto.setTag2Skip(rs.getInt("TAG2SKIP"));
				dto.setTag3Skip(rs.getInt("TAG3SKIP"));
			}
			return dto;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
}
